-----------------------------------------------------
-- Create database for Old Stuff Shopping Services --
-----------------------------------------------------
USE master
GO

IF NOT EXISTS (SELECT name
				FROM sys.databases
				WHERE name = 'OldStuffShopping')

CREATE DATABASE OldStuffShopping
-- DROP DATABASE OldStuffShopping
GO

------------------------------------------------
-- Table : UserAccount
-- Description: Store information of user
------------------------------------------------
USE OldStuffShopping
GO

IF EXISTS (SELECT name
				FROM sys.tables
				WHERE name = 'UserAccount')
BEGIN
	DROP TABLE [UserAccount]
END

GO
CREATE TABLE [UserAccount]
(
	[Id] uniqueidentifier PRIMARY KEY NOT NULL,	
	[FirstName] nvarchar(32) NOT NULL,
	[LastName] nvarchar(32) NOT NULL,
	[Password] varchar(40) NOT NULL,
	[Email] varchar(30) NOT NULL UNIQUE,
	[Properties] nvarchar(max),
	[CreatedDateTime] datetime NOT NULL,
	[ModifiedDateTime] datetime NOT NULL
)

------------------------------------------------
-- Table : Category
-- Description: Stuff category
------------------------------------------------
USE OldStuffShopping
GO

IF EXISTS (SELECT name
				FROM sys.tables
				WHERE name = 'Category')
BEGIN
	DROP TABLE [Category]
END

GO
CREATE TABLE [Category]
(
	[Id] int identity(1,1) PRIMARY KEY NOT NULL,
	[ParentId] int,
	[Name] varchar(30) NOT NULL,
	[Description] nvarchar(max),
	[Properties] nvarchar(max),
	[CreatedDateTime] datetime NOT NULL,
	[ModifiedDateTime] datetime NOT NULL,

	CONSTRAINT FKCategory$is_subcategory_of$Category
		FOREIGN KEY ([ParentId]) REFERENCES [Category]([Id])
)

------------------------------------------------
-- Table : UserStuff
-- Description: Store information of stuff
------------------------------------------------
USE OldStuffShopping
GO

IF EXISTS (SELECT name
				FROM sys.tables
				WHERE name = 'UserStuff')
BEGIN
	DROP TABLE [UserStuff]
END

GO
CREATE TABLE [UserStuff]
(
	[Id] int identity(1,1) PRIMARY KEY NOT NULL,
	[UserId] uniqueidentifier NOT NULL,
	[CategoryId] int NOT NULL,
	[Name] nvarchar(64) NOT NULL,
	[Quantity] int NOT NULL CHECK ([Quantity] >= 0),
	[Description] nvarchar(max),	
	[UnitPrice] decimal(20,6) NOT NULL CHECK ([UnitPrice] >= 0),
	[Status] int DEFAULT 0, -- 0: Published; 1: Unpublished
	[Properties] nvarchar(max),
	[CreatedDateTime] datetime NOT NULL,
	[ModifiedDateTime] datetime NOT NULL,
	
	CONSTRAINT FKUserStuff$stuff_belong_to$UserAccount
		FOREIGN KEY ([UserId]) REFERENCES [UserAccount]([Id]),

	CONSTRAINT KFUserStuff$stuff_belong_to$Category
		FOREIGN KEY ([CategoryId]) REFERENCES [Category]([Id])
)

------------------------------------------------
-- Table : Ranking
-- Description: User ranking information
------------------------------------------------

IF EXISTS (SELECT name
				FROM sys.tables
				WHERE name = 'Ranking')
BEGIN
	DROP TABLE [Ranking]
END

GO
CREATE TABLE [Ranking]
(
	[UserRankId] uniqueidentifier NOT NULL,
	[ReceiverId] uniqueidentifier NOT NULL,
	[Rank] int DEFAULT 0,

	CONSTRAINT PKRanking PRIMARY KEY ([UserRankId], [ReceiverId]),

	CONSTRAINT FKUserRankId$reference$UserId
		FOREIGN KEY ([UserRankId]) REFERENCES [UserAccount]([Id]),
	CONSTRAINT FKReceiverId$reference$UserId
		FOREIGN KEY ([ReceiverId]) REFERENCES [UserAccount]([Id]),

	CONSTRAINT CKRank CHECK ([Rank] BETWEEN 0 AND 5)
)

------------------------------------------------
-- Table : UserStuff
-- Description: Store information of stuff
------------------------------------------------
USE OldStuffShopping
GO

IF EXISTS (SELECT name
				FROM sys.tables
				WHERE name = 'Order')
BEGIN
	DROP TABLE [Order]
END

GO
CREATE TABLE [Order]
(
	[Id] int identity(1,1) PRIMARY KEY NOT NULL,	
	[StuffId] int NOT NULL,
	[UserOrderId] uniqueidentifier NOT NULL,
	[Status] int NOT NULL DEFAULT 0, -- 0: Pending; 1: Accepted; 2: Denied
	[OfferMoney] decimal(20,6) DEFAULT 0,
	[PaymentType] int NOT NULL DEFAULT 0, -- 0: Meet in person; 1: Transfer; 2: Postal; 3: Online
	[Properties] nvarchar(max),
	[CreatedDateTime] datetime NOT NULL,
	[ModifiedDateTime] datetime NOT NULL,
	
	CONSTRAINT FKOrderList$order_stuff$UserStuff
		FOREIGN KEY ([StuffId]) REFERENCES [UserStuff]([Id]),

	CONSTRAINT FKOrderLlist$user_order_the_stuff$UserAccount
		FOREIGN KEY ([UserOrderId]) REFERENCES [UserAccount]([Id])
)

------------------------------------------------
-- Table : NLog & NLogPerformance
-- Description: logging tables
------------------------------------------------

IF EXISTS (SELECT name
				FROM sys.tables
				WHERE name = 'Nlog')
BEGIN
	DROP TABLE [NLog]
END

CREATE TABLE [NLog] 
(
	[Id] int IDENTITY(1,1) NOT NULL,
    [HostAddress] nvarchar(32) NOT NULL,
    [Message] nvarchar(max) NOT NULL,
    [Controller] nvarchar(64) NOT NULL,
    [Action] nvarchar(64) NOT NULL,
    [Path] nvarchar(1024) NOT NULL,    
    [CreatedDateTime] datetime NOT NULL
)

IF EXISTS (SELECT name
				FROM sys.tables
				WHERE name = 'NlogPerformance')
BEGIN
	DROP TABLE [NLogPerformance]
END

GO
CREATE TABLE [NLogPerformance] 
(
	[Id] int IDENTITY(1,1) NOT NULL,
    [HostAddress] nvarchar(32) NOT NULL,
    [ExecutionTime] nvarchar(20) NOT NULL,
    [Controller] nvarchar(64) NOT NULL,
    [Action] nvarchar(64) NOT NULL,
    [Path] nvarchar(1024) NOT NULL,
    [CreatedDateTime] datetime NOT NULL
)